﻿using NB.MGJ.FrameWork;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NB.MGJ.Model;
using MySql.Data.MySqlClient;
using NB.MGJ.Common;
using System.Data;


namespace NB.MGJ.Service
{

    /// <summary>
    /// 促销活动
    /// </summary>
    /// =======================================================================
    /// 类名：MarketService MarketService的备注：MarketService~1<br/>
    /// =======================================================================<br/>
    /// 更新履历<br/>
    /// 序号           更新日期          责任人           更新内容<br/>
    /// 001           2017/8/16        段扬             新建<br/>
    /// =======================================================================<br/>
    public partial class MarketService
    {
        public BaseRepository _baseRepository = new BaseRepository();

        DBHService _dhb = new DBHService();

        #region 奖池管理

        #region 奖池分页
        /// <summary>
        /// 分页  奖池
        /// </summary>
        /// <param name="model">Marketing_Jackpot</param>
        /// <param name="pager">PagerModel</param>
        /// <returns>PagedList</returns>
        public PagedList<Marketing_Jackpot> GetJackpotPager(ref PagerModel pager)
        {
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append(" ");
            strSql1.Append("ID,");
            strSql1.Append("type,");
            strSql1.Append("Name,");
            strSql1.Append("UseCondition,");
            strSql1.Append("Remark,");
            strSql1.Append("TotalNums,");
            strSql1.Append("TotalMoney,");
            strSql1.Append("ResidueNums,");
            strSql1.Append("ResidueMoney,");
            strSql1.Append("Mall,");
            strSql1.Append("CouponType,");
            strSql1.Append("Denomination,");
            strSql1.Append("MaxDenomination,");
            strSql1.Append("MinDenomination,");
            strSql1.Append("StartDate,");
            strSql1.Append("EndDate,");
            strSql1.Append("ShopUrl,");
            strSql1.Append("IsAuthorization,");
            strSql1.Append("EnterpriseId,");
            strSql1.Append("ViewFlag,");
            strSql1.Append("ViewDel,");
            strSql1.Append("Sequence,");
            strSql1.Append("ChangeLoginID,");
            strSql1.Append("ChangeLoginName,");
            strSql1.Append("ChangeDate");
            //var strSql = new StringBuilder(strSql1.ToString().Trim(','));
            pager.FldName = strSql1.ToString();
            pager.TblName = " Marketing_Jackpot ";
            if (pager.StrCondition == "" || pager.StrCondition == null)
            {
                pager.StrCondition = " 1=1   ";
            }
            if (string.IsNullOrEmpty(pager.FldSort) || pager.FldSort == " ")
                pager.FldSort = " ChangeDate desc ";

            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<Marketing_Jackpot> resultList = _baseRepository.ExecuteStoredProcedureCollection<Marketing_Jackpot>("Common_PageList", parameters);
            return new PagedList<Marketing_Jackpot>(resultList, pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value)); ;

        }
        #endregion

        #region 查询 奖池（获取单个MODEL）
        /// <summary>
        /// 根据主键查询表数据
        /// </summary>
        /// <param name=" ButtonId">string 主键--查询条件</param>
        /// <returns>DataTable 查询结果集</returns>
        public Marketing_Jackpot GetJackpotModel(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select ");
            strSql.Append("ID,");

            strSql.Append("type ,");
            strSql.Append("Name ,");
            strSql.Append("UseCondition ,");
            strSql.Append("Remark ,");
            strSql.Append("TotalNums ,");
            strSql.Append("TotalMoney ,");
            strSql.Append("ResidueNums ,");
            strSql.Append("ResidueMoney ,");
            strSql.Append("Mall ,");
            strSql.Append("CouponType ,");
            strSql.Append("Denomination ,");
            strSql.Append("MaxDenomination ,");
            strSql.Append("MinDenomination ,");
            strSql.Append("StartDate ,");
            strSql.Append("EndDate ,");
            strSql.Append("ShopUrl ,");
            strSql.Append("IsAuthorization ,");
            strSql.Append("EnterpriseId ,");
            strSql.Append("ViewFlag ,");
            strSql.Append("ViewDel ,");
            strSql.Append("Sequence ,");
            strSql.Append("ChangeLoginID ,");
            strSql.Append("ChangeLoginName ,");
            strSql.Append("ChangeDate ,");
            strSql.Append("DataID1_LT ,");
            strSql.Append("DataID2_YD ,");
            strSql.Append("DataID3_DX ");
            strSql = new StringBuilder(strSql.ToString());
            strSql.Append(" from Marketing_Jackpot ");
            strSql.Append(" where  ID='" + ID + "'");

            MySqlParameter[] parameters = {
                    new MySqlParameter("@ID",ID)
            };

            Marketing_Jackpot model = _baseRepository.ExecuteText<Marketing_Jackpot>(strSql.ToString(), parameters);

            return model;
        }
        #endregion

        #region 添加奖池
        /// <summary>
        /// 添加奖池
        /// </summary>
        /// <param name="model">奖池</param>
        /// <returns></returns>
        public int AddJackpot(Marketing_Jackpot model)
        {
            int result = _baseRepository.InsertReturnID<Marketing_Jackpot>(model);
            return result;
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model">奖池</param>
        /// <returns></returns>
        public int UpdJackpot(Marketing_Jackpot model)
        {
            return _baseRepository.Update<Marketing_Jackpot>(model);
        }
        #endregion

        #region 删除

        /// <summary>
        /// 检查奖池是否绑定活动
        /// </summary>
        /// <returns></returns>
        public int JackpotBindActivity(int jId) 
        {
            string sql =string.Format( @"SELECT
                                          count(*)
                                         FROM
                                         	marketing_activity a
                                         LEFT JOIN marketing_awards aw ON aw.ActivityId = a.id
                                         LEFT JOIN marketing_jackpot j ON aw.JackpotID = j.ID
                                         where j.ID={0} and a.ViewDel!=1 
                                         ",jId);
         int count=Convert.ToInt32( _baseRepository.GetSingle(sql));
         return count;
        }


        /// <summary>
        /// 通过主键删除方法
        /// </summary>
        /// <param name="  ButtonId">string 主键--删除条件</param>
        /// <returns>int 0为失败 ；1为成功</returns>
        public int DelJackpot(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" delete from Marketing_Jackpot  ");
            strSql.Append(" where  ID='" + ID + "'");
            int result = _baseRepository.ExecuteSql<int>(strSql.ToString());
            return result;
        }
        #endregion

        #region  奖池下拉框 type: 1 红包 2 优惠券 3流量包
        /// <summary>
        /// 奖池下拉框 type: 1 红包 2 优惠券 3流量包
        /// </summary>
        /// <returns></returns>
        public List<Marketing_Jackpot> JackpotBox(string type, int eid)
        {
            string sql = " select * from Marketing_Jackpot where type=" + type + " and EnterpriseId=" + eid + " and ViewDel=0  and   ViewFlag=1  order by changeDate desc";
            return _baseRepository.ExecuteSqlCollection<Marketing_Jackpot>(sql).ToList();
        }

        #endregion

        #region 授权
        /// <summary>
        /// 授权  根据ID修改ViewFlag为1
        /// </summary>
        /// <returns></returns>
        public bool Accredit(Marketing_Jackpot model)
        {

            return _baseRepository.Update<Marketing_Jackpot>(model) >= 1 ? true : false;
        }
        #endregion

        #region 奖池关联活动
        public PagedList<Marketing_Activity> GetJackpot_Activity(ref PagerModel pager)
        {
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append(" ");
            strSql1.Append("a.totalNums,");
            strSql1.Append("a.predictMaxMoney,");
            strSql1.Append("a.predictMinMoney,");
            strSql1.Append("a. NAME,");
            strSql1.Append("ac.title,");
            strSql1.Append("ac.Batch, ");
            strSql1.Append("ac.StartDate, ");
            strSql1.Append("ac.endDate,     ");
            strSql1.Append("ac.RuleExplain,      ");
            strSql1.Append("ac.ActivityRegion,       ");
            strSql1.Append("ac.viewflag      ");

            //var strSql = new StringBuilder(strSql1.ToString().Trim(','));
            pager.FldName = strSql1.ToString();
            pager.TblName = @" marketing_jackpot j
                                        LEFT JOIN marketing_awards a ON a.JackpotID = j.id
                                        LEFT JOIN marketing_activity ac ON a.ActivityId = ac.ID ";
            if (pager.StrCondition == "" || pager.StrCondition == null)
            {
                pager.StrCondition = " 1=1   ";
            }
            if (string.IsNullOrEmpty(pager.FldSort) || pager.FldSort == " ")
                pager.FldSort = " a.id desc ";

            pager.StrCondition += " and ac.Title   is not  NULL";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<Marketing_Activity> resultList = _baseRepository.ExecuteStoredProcedureCollection<Marketing_Activity>("Common_PageList", parameters);
            return new PagedList<Marketing_Activity>(resultList, pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value)); ;

        }
        #endregion

        #region 当月 或 当日 奖池发送累计送出的总数
        /// <summary>
        /// 获取奖池发送数量 和金额  根据日期获取
        /// </summary>
        /// <param name="type">奖池类型</param>
        /// <param name="date">时间</param>
        /// <returns></returns>
        public DataTable GetJackpotSendNum(int type, string date, string code)
        {
            string sql = string.Format(@"select SUM(Denomination) AS money,COUNT(*) AS count from marketing.marketing_thewinning{0} 
                           where  AwardsID in ( select a.id from marketing_jackpot j left join
                           marketing_awards a on a.JackpotID=j.id where type={1} ) and  TIME LIKE '{2}%'", code, type, date);
            return _baseRepository.GetDataTable(sql);

        }


        #endregion

        #region 当日 7日  奖池发送累计送出
        /// <summary>
        /// 获取奖池发送数量 和金额  根据日期获取
        /// </summary>
        /// <param name="type">奖池类型</param>
        /// <param name="dayNum">天数</param>
        /// <returns></returns>
        public IEnumerable<Marketing_TheWinning> GetJackpotSendNumList(int jId, int type, int dayNum, string code)
        {
            if (!_dhb.TabExists("marketing","marketing_thewinning"+code))
            {
                return new List<Marketing_TheWinning>();
            }
            string sql = string.Format(@"select SUM(Denomination) as sendSumMoney ,DATE_FORMAT( time, '%Y-%m-%d' ) as SendTime from marketing.marketing_thewinning{0} 
                           where  AwardsID in ( select a.id from marketing_jackpot j left join
                           marketing_awards a on a.JackpotID=j.id where type={1} and j.Id={2} ) and TO_DAYS(NOW()) - TO_DAYS(Time) <= {3} GROUP BY DATE_FORMAT( time, '%Y-%m-%d')  "
                           , code, type, jId, dayNum);
            return _baseRepository.ExecuteTextCollection<Marketing_TheWinning>(sql).ToList();
        }

        #endregion

        #region 获取奖池发放数量 金额，和剩余金额 数量

        /// <summary>
        /// 获取奖池发放数量 金额，和剩余金额 数量
        /// </summary>
        /// <returns></returns>
        public DataTable GetJackpotSendResidue(int jId, string code)
        {
            string sql = string.Format(@"select SUM(Denomination) as sendMoney,count(*) as sendCount  from marketing.marketing_thewinning{0}
                           where  AwardsID in ( select a.id from marketing_jackpot j left join
                           marketing_awards a on a.JackpotID=j.id where j.id={1})", code, jId);
            return _baseRepository.GetDataTable(sql);
        }
        #endregion

        #endregion

        #region 营销活动

        #region 绑定批次下拉框 根据机构ID
        /// <summary>
        /// 绑定下拉框 根据机构ID 获取数据ID,BatchName     总部
        /// </summary>
        /// <param name="EID"></param>
        /// <returns></returns>
        public List<CodeManage_GenCodeBatch> GetGenCodeListBox(int EID, string batchID)
        {
            
            string NotInBatchSql =string.Format( " SELECT group_concat(Batch) FROM marketing_activity WHERE EnterpriseId ='{0}' and ViewDel = 0 ",EID);
            string NotInBatch = _baseRepository.GetDataTable(NotInBatchSql).Rows[0][0].ToString();
            NotInBatch = NotInBatch == "" ? "0" : NotInBatch;
            List<CodeManage_GenCodeBatch> resultList = new List<CodeManage_GenCodeBatch>();
            string sqlItem = string.Empty;
            batchID = batchID == "" ? "0" : batchID;
            string sql = @"select b.ID,b.Batch,c.Gen_Code_Nums  from CodeManage_GenCodeBatch b left join codemanage_gencode c on c.PatchId=b.ID where 	ViewDel=0 {0} and ViewFlag=1 order by  Batch desc ";
            sqlItem += @" and b.EnterpriseId='" + EID + "' and ((Batch NOT IN ( " + NotInBatch + " )  and Batch not in (select distinct  allotBatch from  codemanage_allot where HasVerifie=1 and f_EnterpriseId='" + EID + "')) or   Batch  in (" + batchID + "))";
            sql = string.Format(sql, sqlItem);
            // sql += " and  Batch not in (select Batch from Marketing_Activity where b.EnterpriseId='" + EID + "' )  or Batch=" + batchID + " ";
            resultList = _baseRepository.ExecuteSqlCollection<CodeManage_GenCodeBatch>(sql).ToList();
            return resultList;
        }

        /// <summary>
        /// 绑定下拉框 根据机构ID 获取数据ID,BatchName     子机构
        /// </summary>
        /// <param name="EID"></param>
        /// <returns></returns>
        public List<CodeManage_GenCodeBatch> GetGenCodeListBox2(int eId, string batchID)
        {
            string NotInBatchSql = string.Format(" SELECT group_concat(Batch) FROM marketing_activity WHERE EnterpriseId ='{0}' and ViewDel = 0 ", eId);
            string NotInBatch = _baseRepository.GetDataTable(NotInBatchSql).Rows[0][0].ToString();
            NotInBatch = NotInBatch == "" ? "0" : NotInBatch;
            batchID = batchID == "" ? "0" : batchID;
            List<CodeManage_GenCodeBatch> resultList = new List<CodeManage_GenCodeBatch>();
            string sql = string.Format("select  allotBatch  Batch, AllotCount Gen_Code_Nums  from  codemanage_allot  where HasVerifie=1 and t_EnterpriseId='{0}'   and  (ApplyCount=allotCount  or  AllotType=1) ", eId);
            sql += " and (allotBatch NOT IN ( " + NotInBatch + " ) 	OR allotBatch IN (" + batchID + "))  ";
            resultList = _baseRepository.ExecuteSqlCollection<CodeManage_GenCodeBatch>(sql).ToList();
            return resultList;

        }

        #endregion

        #region 活动数据操作
        /// <summary>
        /// 删除活动关联的所有数据
        /// </summary>
        /// <returns></returns>
        public int DelActivity(Marketing_Activity model) 
        {
            
                string str = @"delete from marketing_activity where id={0};
                           delete from marketing_activityinfo where id={1};
                           delete from marketing_awards where activityId={2};
                           delete from marketing_delivery where id={3};
                           delete from marketing_template where id={4};";
                string sql = string.Format(str, model.Id, model.ActivityInfoID, model.Id, model.DeliveryId, model.TemplateID);
                return _baseRepository.ExecuteText<int>(sql);
     
        }

        /// <summary>
        /// 修改活动
        /// </summary>
        /// <param name="Aid">活动ID</param>
        /// <returns></returns>
        public int UpdActivity(Marketing_Activity model)
        {
            return _baseRepository.Update<Marketing_Activity>(model);
        }

        /// <summary>
        /// 活动分页数据
        /// </summary>
        /// <param name="pager"></param>
        /// <returns></returns>
        public PagedList<Marketing_Activity> GetActivityList(ref PagerModel pager, string Asearch, string eCode)
        {
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append(" ");
            strSql1.Append("ID,");
            strSql1.Append("EnterpriseId,");
            strSql1.Append("Title,");
            strSql1.Append("StartDate,");
            strSql1.Append("EndDate,");
            strSql1.Append("ActivityRegion,");
            strSql1.Append("RewardObject,");
            strSql1.Append("RuleExplain,");
            strSql1.Append("Batch,");
            strSql1.Append("Remark,");
            strSql1.Append("ActivityType,");
            strSql1.Append("ViewFlag,");
            strSql1.Append("ViewDel,");
            strSql1.Append("ChangeLoginID,");
            strSql1.Append("ChangeLoginName,");
            strSql1.Append("ChangeDate,");
            string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string table = "marketing_thewinning" + eCode;
            if (_dhb.TabExists("marketing", table))
            {
                //改活动今日扫码数量
                strSql1.Append("(select count(ID) from marketing.marketing_thewinning" + eCode + " where ActivityID=a.ID and Time like '" + date.Substring(0, 10) + "%') as scanCount,");
                //改活动扫码总数量
                strSql1.Append("(select count(ID) from marketing.marketing_thewinning" + eCode + " where ActivityID=a.ID  ) as sumScanCount ");
            }
            else
            {
                //改活动今日扫码数量
                strSql1.Append(" 0 as scanCount,");
                //改活动扫码总数量
                strSql1.Append(" 0 as sumScanCount ");
            }

            pager.FldName = strSql1.ToString();
            pager.TblName = " Marketing_Activity a ";
            if (string.IsNullOrEmpty(pager.FldSort) || pager.FldSort == " ")
                pager.FldSort = " ChangeDate desc ";
            pager.StrCondition += " and ViewDel=0 ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<Marketing_Activity> resultList = _baseRepository.ExecuteStoredProcedureCollection<Marketing_Activity>("Common_PageList", parameters).ToList();
            return new PagedList<Marketing_Activity>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }

        /// <summary>
        /// 添加活动model数据（通过传递实体类添加数据，把添加的数据赋值给实体类属性，不添加的值不填写）
        /// </summary>
        /// <param name="Marketing_Activity">Marketing_Activity实体类</param>
        /// <returns>int 0为失败 ；1为成功</returns>
        public int AddActivity(Marketing_Activity model)
        {
            return _baseRepository.InsertReturnID<Marketing_Activity>(model);
        }

        #region 查询活动表（获取单个MODEL）
        /// <summary>
        /// 根据主键查询活动表数据 返回Model
        /// </summary>
        /// <param name=" ButtonId">string 主键--查询条件</param>
        /// <returns>DataTable 查询结果集</returns>
        public Marketing_Activity GetActivityModel(int ID)
        {
            string sql = "select *,(select EnterpriseName from organization_enterprise where id=marketing_activity.EnterpriseId) as EnterpriseName  from Marketing_Activity where  ID=" + ID;
            return _baseRepository.ExecuteText<Marketing_Activity>(sql);
        }
        #endregion

        #region 根据机构ID 批次号ID  寻找活动
        /// <summary>
        /// 根据机构ID 和批次ID 查找活动
        /// </summary>
        /// <param name="EID">机构</param>
        /// <param name="BID">批次</param>
        /// <returns>活动model</returns>
        public Marketing_Activity GetActivity(int EID, int BID)
        {
            Marketing_Activity model = new Marketing_Activity();
            string sql = string.Format("select * from Marketing_Activity where EnterpriseId={0} and batch={1} ", EID, BID);
            return _baseRepository.ExecuteSqlCollection<Marketing_Activity>(sql).ToList().FirstOrDefault();
        }

        /// <summary> 
        /// 获取发布的活动  根据机构ID 批次号ID 
        /// </summary>
        /// <param name="EID">机构</param>
        /// <param name="BID">批次</param>
        /// <returns>活动model</returns>
        public Marketing_Activity GetActivity_Issue(int EID, string BID)
        {
            Marketing_Activity model = new Marketing_Activity();
            string sql = "select * from Marketing_Activity ";
            sql += string.Format(@" where viewFlag=1 and ViewDel=0 and EnterpriseId={0} and find_in_set('{1}',Batch) and 
                                             startDate<='{2}'  and endDate>'{3}'  "
                                            , EID, BID, DateTime.Now, DateTime.Now);
            WeiXinHelper.WriteLog("GetActivity_Issue",sql);
            return _baseRepository.ExecuteText<Marketing_Activity>(sql);
        }

        #endregion


        #region 活动启用禁用
        //活动启用时
        public int updateMoneyOfOk(int aId)
        {
            string sql = "update marketing_jackpot a right join (select sum(ResidueMoney)as residuemoney ,  sum(ResidueNums) AS ResidueNums,jackpotid from marketing_awards  where activityid={0} group by jackpotid) b on a.id=b.jackpotid set a.residuemoney = (a.residuemoney - b.residuemoney),a.ResidueNums=(a.ResidueNums-b.ResidueNums)where a.ID=b.jackpotid";
            sql = string.Format(sql, aId);
            return _baseRepository.ExecuteSql<int>(sql);
        }
        //活动禁用
        public int updateMoneyOfNot(int aId)
        {
            string sql = "update marketing_jackpot a right join (select sum(residuemoney)as residuemoney ,	  sum(ResidueNums) AS ResidueNums,jackpotid from marketing_awards  where activityid={0} group by jackpotid) b on a.id=b.jackpotid set a.residuemoney=(a.residuemoney+b.residuemoney),a.ResidueNums=(	a.ResidueNums + b.ResidueNums) where a.ID=b.jackpotid";
            sql = string.Format(sql, aId);
            return _baseRepository.ExecuteSql<int>(sql);
        }
        #endregion

        #endregion


        #region  派送方式数据操作
        /// <summary>
        /// 添加派送方式
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddDelivery(Marketing_Delivery model)
        {
            return _baseRepository.InsertReturnID<Marketing_Delivery>(model);
        }
        public int UpdateDelivery(Marketing_Delivery model)
        {
            return _baseRepository.Update<Marketing_Delivery>(model);
        }
        #endregion

        #region 奖项数据操作
        /// <summary>
        /// 奖项数据添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddAwards(Marketing_Awards model)
        {
            return _baseRepository.Insert<Marketing_Awards>(model);
        }

         /// <summary>
         /// 奖项集合添加
         /// </summary>
         /// <param name="awardsList"></param>
        public void AwardsAddorUpd(List<Marketing_Awards> awardsList)
        {
            string ids="";
            foreach (Marketing_Awards item in awardsList)
            {

                if (item.Id == 0)
                {
                    ids += _baseRepository.InsertReturnID<Marketing_Awards>(item) + ",";
                }
                else
                {
                    ids += item.Id+",";
                    _baseRepository.Update<Marketing_Awards>(item);
                }
            }

            string sql = "delete from marketing_awards where ActivityId="+awardsList[0].ActivityId+" and id not in ("+ids.TrimEnd(',')+")";
            _baseRepository.ExecuteSql<int>(sql);
        }

        /// <summary>
        /// 单个奖项添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int InsertAwards(Marketing_Awards model)
        {
            return _baseRepository.Insert<Marketing_Awards>(model);
        }

        /// <summary>
        /// 根据主键修改数据修改
        /// </summary>
        /// <param name=" ButtonId"> Marketing_Activity实体类 需要修改的列赋值给实体类的属性,不修改的不填写</param>
        /// <returns>int 0为失败 ;1为成功</returns>
        public int UpdateAwards(Marketing_Awards model)
        {
            return _baseRepository.Update<Marketing_Awards>(model);
        }

 
        /// <summary>
        /// 根据主键查询奖项表数据
        /// </summary>
        /// <param name=" ButtonId">string 主键--查询条件</param>
        /// <returns>DataTable 查询结果集</returns>
        public Marketing_Awards GetAwardsModel(int awId)
        {
            string sql = string.Format("select type,a.*,e.EnterpriseName,j.Denomination from Marketing_Awards a left join Organization_Enterprise e on a.EnterpriseId=e.ID  left join Marketing_Jackpot j on j.ID=a.JackpotID where a.ID={0} order by a.Sequence desc ", awId);
       
            return _baseRepository.ExecuteText<Marketing_Awards>(sql);
        }

        public void DelAwardsList(int Aid) 
        {
            string sql = "delete from marketing_awards where activityId="+Aid;
              _baseRepository.ExecuteSql<int>(sql);
        }

        #endregion

        #region 活动详情 数据操作
        /// <summary>
        /// 活动高级设置数据添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddActivityInfo(Marketing_ActivityInfo model)
        {
            return _baseRepository.InsertReturnID<Marketing_ActivityInfo>(model);
        }
        /// <summary>
        /// 修改活动详情数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateActivityInfo(Marketing_ActivityInfo model)
        {
            return _baseRepository.Update<Marketing_ActivityInfo>(model);
        }

        /// <summary>
        /// 获取活动详细信息
        /// </summary>
        /// <param name="aInfoId"></param>
        /// <returns></returns>
        public Marketing_ActivityInfo GetActivityInfo(int aInfoId)
        {
            string sql = "select * from marketing_activityinfo where ID=" + aInfoId;
            return _baseRepository.ExecuteText<Marketing_ActivityInfo>(sql);
        }

        #endregion
          
 
        #endregion 

        #region 中奖明细

        /// <summary>
        /// 中奖列表
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public PagedList<Marketing_TheWinning> GetTheWinningList(ref PagerModel pager, string code)
        {

            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("t.*,");
            strSql1.Append("u.`phone`,u.Nickname,u.HeadImgUrl,");//用户信息
            strSql1.Append("a.Title,");//活动名称
            strSql1.Append("aw.`Name`,aw.AwardsGrade,");//奖项名称
            strSql1.Append("j.type");

            pager.FldName = strSql1.ToString();
            pager.TblName = " marketing.marketing_thewinning" + code + " t LEFT JOIN marketing.marketing_user" + code + " u ON t.UserID = u.ID LEFT JOIN nbsecuritymain.marketing_activity a ON a.ID = t.ActivityID left join nbsecuritymain.marketing_awards aw on t.AwardsID=aw.ID LEFT Join nbsecuritymain.marketing_jackpot j on aw.JackpotID=j.ID ";
            if (string.IsNullOrEmpty(pager.FldSort) || pager.FldSort == "")
                pager.FldSort = " t.time desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            DataTable dt = new DataTable();

            IEnumerable<Marketing_TheWinning> resultList = _dhb.TabExists("marketing", "marketing_thewinning" + code)
                ? _baseRepository.ExecuteStoredProcedureCollection<Marketing_TheWinning>("Common_PageList", parameters).ToList()
                : new List<Marketing_TheWinning>();
            return new PagedList<Marketing_TheWinning>(resultList, pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));


        }

        /// <summary>
        /// 获取中奖纪录详细 
        /// </summary>
        /// <param name="Tid"></param>
        /// <returns></returns>
        public Marketing_TheWinning GetTheWinning(int Tid, string eCode)
        {
            string sql = @"SELECT
	                       t.*, aw.Congrats as AwCongrats,
	                       aw.Remark as AwRemark,
	                       aw.AwardsGrade,
	                       aw.AwardsIosUrl,
	                       aw. NAME as AwName,
	                       a.Title,
                            e.EnterpriseName,
                           aw.Congrats,
                           j.type
                           FROM
                           	marketing.marketing_thewinning{0} t
                           LEFT JOIN marketing_activity a ON t.ActivityID = a.ID
                           LEFT JOIN marketing_awards aw ON t.AwardsID = aw.ID
                           LEFT JOIN marketing_jackpot j on aw.JackpotID=j.ID
                           LEFT JOIN organization_enterprise e on  a.EnterpriseId=e.id
                           WHERE
	                       t.id =" + Tid;
            return _baseRepository.ExecuteText<Marketing_TheWinning>(string.Format(sql, eCode));
        }

 

        #endregion

        #region 用户管理
        /// <summary>
        /// 获取选择的用户 派送奖品
        /// </summary>
        /// <param name="Ids">用户ID集合</param>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<Marketing_User> GetMarketingList(string Ids, string code)
        {
            string sql = @"select * from  {0} where Id in({1})";
            return _baseRepository.ExecuteSqlCollection<Marketing_User>(string.Format(sql, "marketing.Marketing_User" + code, Ids)).ToList();
        }

        /// <summary>
        /// 用户中奖记录      分页
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public PagedList<Marketing_TheWinning> GetUserDealList(ref PagerModel pager, string code)
        {
            
                StringBuilder strSql1 = new StringBuilder();
                strSql1.Append(" u.HeadImgUrl, ");
                strSql1.Append("u.`Name`,");
                strSql1.Append("u.Nickname,");
                strSql1.Append("a.Title,");
                strSql1.Append("aw.AwardsGrade,");
                strSql1.Append("aw.ButtonName,");
                strSql1.Append("t.*");

                //var strSql = new StringBuilder(strSql1.ToString().Trim(','));
                pager.FldName = strSql1.ToString();
                pager.TblName = string.Format(@"	marketing.marketing_thewinning{0} t
                                LEFT JOIN marketing_activity a ON t.ActivityID = a.ID
                                left join marketing.marketing_user{1} u on t.UserID=u.ID
                                LEFT JOIN marketing_awards aw on t.AwardsID=aw.ID", code, code);
                if (pager.StrCondition == "" || pager.StrCondition == null)
                {
                    pager.StrCondition = " 1=1   ";
                }
                if (string.IsNullOrEmpty(pager.FldSort) || pager.FldSort == " ")
                    pager.FldSort = " t.Time desc ";


                MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
                IEnumerable<Marketing_TheWinning> resultList = _baseRepository.ExecuteStoredProcedureCollection<Marketing_TheWinning>("Common_PageList", parameters);
                return new PagedList<Marketing_TheWinning>(resultList, pager.Page, pager.PageSize, 1);
         
        
        }

        #endregion

    
       
    }
}
